import time

import pandas as pd
import requests
import datetime
from datetime import timezone
from utils import df_into_db
from utils import df_into_db, read_sql
import numpy as np


def get_difference_statistics(series1, series2, frequency):
    # 基本统计
    total_elements_series1 = len(series1)
    total_elements_series2 = len(series2)
    assert total_elements_series1 == total_elements_series2

    # 数值Series的统计差异
    if pd.api.types.is_numeric_dtype(series1) and pd.api.types.is_numeric_dtype(series2):
        absolute_diff = np.abs(series1 - series2)
        if frequency == "1d":
            print("对比binance和okx的日线数据")
        else:
            print("对比binance和okx的小时线数据")
        print(f"时间范围: {series1.index.tolist()[0]}到{series1.index.tolist()[-1]}")
        print("绝对指标: abs(binance收盘价序列-okx收盘价序列)")
        print(absolute_diff.describe(percentiles=[0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99]))
        print(f"最大值对应的日期:{absolute_diff.idxmax()}")
        relative_diff = absolute_diff / np.abs(series1)
        print("相对指标: abs(binance收盘价序列-okx收盘价序列)/binance收盘价序列")
        print(relative_diff.describe(percentiles=[0.1, 0.25, 0.5, 0.75, 0.9, 0.95, 0.99]))
        error_count = len(relative_diff[relative_diff >= 0.01])
        error_ratio = error_count/len(relative_diff)
        print(f"误差超过1%的数量: {error_count}, 占比:{error_ratio}")
        print(f"最大值对应的日期:{relative_diff.idxmax()}")


def compare_diff_datasourec(df1, df2, frequency):
    begin_date = max(df1.iloc[0]["datetime"], df2.iloc[0]["datetime"])
    end_date = min(df1.iloc[-1]["datetime"], df2.iloc[-1]["datetime"])
    df1 = df1.loc[(df1.datetime >= begin_date) & (df1.datetime <= end_date)]
    df2 = df2.loc[(df2.datetime >= begin_date) & (df2.datetime <= end_date)]
    if len(df1) == len(df2):
        print("是否有缺少: 无")
    else:
        print(f"是否有缺失: 有")
    if frequency == '1h':
        # 特殊处理  币安1h的k线不知道为什么有缺失
        print("binance缺失的数据条数:", len(set(df2["datetime"]) - set(df1["datetime"])))
        df2 = df2[~df2.datetime.isin(set(df2["datetime"]) - set(df1["datetime"]))]

    # for price in ["open", "high", "low", "close"]:
    for price in ["close"]:
        # print(f"-------------------------{price}----------------------------")
        ser1 = df1.set_index("datetime")[price]
        ser2 = df2.set_index("datetime")[price]
        get_difference_statistics(ser1, ser2, frequency)


frequency = "1h"
df = read_sql(f"select * from k_line where symbol='BTC' and frequency='{frequency}' "
              f"and datetime < '2025-09-12'"
              f"order by datetime", db_name="all_history_ohlcvm_coinmarketcap")

glassnode_df = df[df["datasource"] == "glassnode"]
binance_df = df[df["datasource"] == "binance"]

okx_df = df[df["datasource"] == "okx"]
okx_index_df = okx_df[okx_df["type"] == "index"]
okx_spot_df = okx_df[okx_df["type"] == "spot"]

compare_diff_datasourec(binance_df, okx_spot_df, frequency)
# compare_diff_datasourec(glassnode_df, okx_spot_df, frequency)



